package com.mp.approvalreq.dao.impl;

import com.mp.approvalreq.dao.LeaveDao;
import com.mp.approvalreq.entity.Leave;
import com.mp.approvalreq.util.ObjectUtils;
import com.plusmoney.util.DateTimeHelper;
import com.plusmoney.util.SQLLogger;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.*;

public class LeaveDaoImpl extends AbstractDao implements LeaveDao {

    private static final Logger logger = LoggerFactory.getLogger(LeaveDaoImpl.class);

    private Connection connection;

    public LeaveDaoImpl(Connection connection) {
        this.connection = connection;
    }

    private PreparedStatement getPreparedStatement(String sql) throws SQLException {
        return connection.prepareStatement(sql);
    }

    /**
     * 统一获取方案查询SQL
     *
     * @param condition 条件（where……[order by……]/[group by ……] [limit……]）
     * @return
     */
    @Override
    String getQuerySql(String condition) {
        final String query = "select id, company_id, company_info_id, request_id, start_date,"
                + " end_date, real_start_time, real_end_time, leave_type, reason,"
                + " duration, show_time, leave_time_unit, leave_calculate_type, leave_time_detail,"
                + " is_leave_pause, pause_by, pause_time, balance_type, state,"
                + " start_day_mode, end_day_mode, leave_type_id, approval_num"
                + " from sys_approval_leave";
        return String.format("%s %s", query, condition);
    }

    @Override
    Leave getObject(ResultSet rs) throws SQLException {
        Leave leave = new Leave();
        leave.setId(rs.getInt(1));
        leave.setCompanyId(rs.getInt(2));
        leave.setCompanyInfoId(rs.getInt(3));
        leave.setRequestId(rs.getInt(4));
        leave.setStartDate(rs.getString(5));

        leave.setEndDate(rs.getString(6));
        leave.setRealStartTime(rs.getTimestamp(7));
        leave.setRealEndTime(rs.getTimestamp(8));
        leave.setLeaveType(rs.getString(9));
        leave.setReason(rs.getString(10));

        leave.setDuration(rs.getInt(11));
        leave.setShowTime(rs.getString(12));
        leave.setLeaveTimeUnit(rs.getInt(13));
        leave.setLeaveCalculateType(rs.getInt(14));
        leave.setLeaveTimeDetail(rs.getString(15));

        leave.setIsLeavePause(rs.getInt(16));
        leave.setPauseBy(rs.getInt(17));
        leave.setPauseTime(rs.getTimestamp(18));
        leave.setBalanceType(rs.getInt(19));
        leave.setState(rs.getInt(20));

        leave.setStartDayMode(rs.getString(21));
        leave.setEndDayMode(rs.getString(22));
        leave.setLeaveTypeId(rs.getInt(23));
        leave.setApprovalNum(rs.getString(24));
        return leave;
    }

    private String getListQuerySql(String condition) {
        final String query = "select company_id, company_info_id, request_id, start_date, end_date,"
                + " real_start_time, real_end_time, leave_type, duration, show_time,"
                + " leave_time_unit, leave_calculate_type, reason, show_time, start_day_mode,"
                + " end_day_mode"
                + " from sys_approval_leave";
        return String.format("%s %s", query, condition);
    }

    private Leave getListObject(ResultSet rs) throws SQLException {
        Leave leave = new Leave();
        leave.setCompanyId(rs.getInt(1));
        leave.setCompanyInfoId(rs.getInt(2));
        leave.setRequestId(rs.getInt(3));
        leave.setStartDate(rs.getString(4));
        leave.setEndDate(rs.getString(5));

        leave.setRealStartTime(rs.getTimestamp(6));
        leave.setRealEndTime(rs.getTimestamp(7));
        leave.setLeaveType(rs.getString(8));
        leave.setDuration(rs.getInt(9));
        leave.setShowTime(rs.getString(10));

        leave.setLeaveTimeUnit(rs.getInt(11));
        leave.setLeaveCalculateType(rs.getInt(12));
        leave.setReason(rs.getString(13));
        leave.setShowTime(rs.getString(14));
        leave.setStartDayMode(rs.getString(15));

        leave.setEndDayMode(rs.getString(16));
        return leave;
    }

    @Override
    public void insertLeave(Leave leave) throws SQLException {
        ObjectUtils.initInteger(leave);
        String sql = "insert into sys_approval_leave set"
                + " company_id = ?, company_info_id = ?, request_id = ?, start_date = ?, end_date = ?,"
                + " real_start_time = ?, real_end_time = ?, leave_type = ?, reason = ?, duration = ?,"
                + " show_time = ?, leave_time_unit = ?, leave_calculate_type = ?, leave_time_detail = ?, balance_type = ?,"
                + " approval_num = ?, start_day_mode = ?, end_day_mode = ?, leave_type_id = ?, create_time = now()";
        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, leave.getCompanyId());
            ps.setInt(2, leave.getCompanyInfoId());
            ps.setInt(3, leave.getRequestId());
            ps.setString(4, leave.getStartDate());
            ps.setString(5, leave.getEndDate());

            ps.setTimestamp(6, new Timestamp(leave.getRealStartTime().getTime()));
            ps.setTimestamp(7, new Timestamp(leave.getRealEndTime().getTime()));
            ps.setString(8, leave.getLeaveType());
            ps.setString(9, leave.getReason());
            ps.setInt(10, leave.getDuration());

            ps.setString(11, leave.getShowTime());
            ps.setInt(12, leave.getLeaveTimeUnit());
            ps.setInt(13, leave.getLeaveCalculateType());
            ps.setString(14, leave.getLeaveTimeDetail());
            ps.setInt(15, leave.getBalanceType());

            ps.setString(16, leave.getApprovalNum());
            ps.setString(17, leave.getStartDayMode());
            ps.setString(18, leave.getEndDayMode());
            ps.setInt(19, leave.getLeaveTypeId());
            logger.info(SQLLogger.logSQL(sql, leave.getCompanyId(), leave.getCompanyInfoId(), leave.getRequestId(),
                    leave.getStartDate(), leave.getEndDate(), leave.getRealStartTime(), leave.getRealEndTime(),
                    leave.getLeaveType(), leave.getReason(), leave.getDuration(), leave.getShowTime(),
                    leave.getLeaveTimeUnit(), leave.getLeaveCalculateType(), leave.getLeaveTimeDetail(), leave.getBalanceType(),
                    leave.getApprovalNum(), leave.getStartDayMode(), leave.getEndDayMode(), leave.getLeaveTypeId()));

            ps.executeUpdate();
        }
    }


    @Override
    public void insertBatchLeave(List<Leave> leaves) throws SQLException {
        String sql = "insert into sys_approval_leave set"
                + " company_id = ?, company_info_id = ?, request_id = ?, start_date = ?, end_date = ?,"
                + " real_start_time = ?, real_end_time = ?, leave_type = ?, reason = ?, duration = ?,"
                + " show_time = ?, leave_time_unit = ?, leave_calculate_type = ?, leave_time_detail = ?, balance_type = ?,"
                + " approval_num = ?, start_day_mode = ?, end_day_mode = ?, leave_type_id = ?, create_time = now()";
        try (PreparedStatement ps = getPreparedStatement(sql)) {
            for (Leave leave : leaves) {
                ObjectUtils.initInteger(leave);
                ps.setInt(1, leave.getCompanyId());
                ps.setInt(2, leave.getCompanyInfoId());
                ps.setInt(3, leave.getRequestId());
                ps.setString(4, leave.getStartDate());
                ps.setString(5, leave.getEndDate());

                ps.setTimestamp(6, new Timestamp(leave.getRealStartTime().getTime()));
                ps.setTimestamp(7, new Timestamp(leave.getRealEndTime().getTime()));
                ps.setString(8, leave.getLeaveType());
                ps.setString(9, leave.getReason());
                ps.setInt(10, leave.getDuration());

                ps.setString(11, leave.getShowTime());
                ps.setInt(12, leave.getLeaveTimeUnit());
                ps.setInt(13, leave.getLeaveCalculateType());
                ps.setString(14, leave.getLeaveTimeDetail());
                ps.setInt(15, leave.getBalanceType());

                ps.setString(16, leave.getApprovalNum());
                ps.setString(17, leave.getStartDayMode());
                ps.setString(18, leave.getEndDayMode());
                ps.setInt(19, leave.getLeaveTypeId());
                logger.info(SQLLogger.logSQL(sql, leave.getCompanyId(), leave.getCompanyInfoId(), leave.getRequestId(),
                        leave.getStartDate(), leave.getEndDate(), leave.getRealStartTime(), leave.getRealEndTime(),
                        leave.getLeaveType(), leave.getReason(), leave.getDuration(), leave.getShowTime(),
                        leave.getLeaveTimeUnit(), leave.getLeaveCalculateType(), leave.getLeaveTimeDetail(), leave.getBalanceType(),
                        leave.getApprovalNum(), leave.getStartDayMode(), leave.getEndDayMode(), leave.getLeaveTypeId()));

                ps.addBatch();
            }

            ps.executeBatch();
        }
    }

    @Override
    public void insertPauseLeaveInfo(Leave leave) throws SQLException {
        ObjectUtils.initInteger(leave);
        String sql = "insert into sys_approval_leave set"
                + " company_id = ?, company_info_id = ?, request_id = ?, start_date = ?, end_date = ?,"
                + " real_start_time = ?, real_end_time = ?, leave_type = ?, reason = ?, duration = ?,"
                + " show_time = ?, leave_time_unit = ?, leave_calculate_type = ?, leave_time_detail = ?, is_leave_pause = ?,"
                + " pause_by = ?, pause_time = ?, balance_type = ?, state = ?, start_day_mode = ?,"
                + " end_day_mode = ?, approval_num = ?, leave_type_id = ?, create_time = now()";
        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, leave.getCompanyId());
            ps.setInt(2, leave.getCompanyInfoId());
            ps.setInt(3, leave.getRequestId());
            ps.setString(4, leave.getStartDate());
            ps.setString(5, leave.getEndDate());

            ps.setTimestamp(6, new Timestamp(leave.getRealStartTime().getTime()));
            ps.setTimestamp(7, new Timestamp(leave.getRealEndTime().getTime()));
            ps.setString(8, leave.getLeaveType());
            ps.setString(9, leave.getReason());
            ps.setInt(10, leave.getDuration());

            ps.setString(11, leave.getShowTime());
            ps.setInt(12, leave.getLeaveTimeUnit());
            ps.setInt(13, leave.getLeaveCalculateType());
            ps.setString(14, leave.getLeaveTimeDetail());
            ps.setInt(15, leave.getIsLeavePause());

            ps.setInt(16, leave.getPauseBy());
            String pauseTime = DateTimeHelper.format(leave.getPauseTime(), DateTimeHelper.DEFAULT_PATTERN);
            ps.setString(17, pauseTime);
            ps.setInt(18, leave.getBalanceType());
            ps.setInt(19, leave.getState());
            ps.setString(20, leave.getStartDayMode());

            ps.setString(21, leave.getEndDayMode());
            ps.setString(22,leave.getApprovalNum());
            ps.setInt(23,leave.getLeaveTypeId());

            logger.info(SQLLogger.logSQL(sql, leave.getCompanyId(), leave.getCompanyInfoId(), leave.getRequestId(),
                    leave.getStartDate(), leave.getEndDate(), leave.getRealStartTime(), leave.getRealEndTime(),
                    leave.getLeaveType(), leave.getReason(), leave.getDuration(), leave.getShowTime(),
                    leave.getLeaveTimeUnit(), leave.getLeaveCalculateType(), leave.getLeaveTimeDetail(), leave.getIsLeavePause(),
                    leave.getPauseBy(), pauseTime, leave.getBalanceType(), leave.getState(), leave.getStartDayMode(),
                    leave.getEndDayMode(),leave.getApprovalNum(),leave.getLeaveTypeId()));

            ps.executeUpdate();
        }
    }

    @Override
    public Leave selectLastLeaveByRequestId(Integer requestId) throws SQLException {
        String sql = getQuerySql("where request_id = ? order by id desc limit 1");

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, requestId);
            logger.info(SQLLogger.logSQL(sql, requestId));

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return getObject(rs);
                }
            }
        }
        return null;
    }

    @Override
    public Leave selectFirstLeaveByRequestId(Integer requestId) throws SQLException {
        String sql = getQuerySql("where request_id = ? order by id limit 1");

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, requestId);
            logger.info(SQLLogger.logSQL(sql, requestId));

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return getObject(rs);
                }
            }
        }
        return null;
    }

    @Override
    public void deleteLeave(Leave leave) throws SQLException {
        String sql = "update sys_approval_leave set state = 2, update_time = now() where id = ? and company_id = ? limit 1";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, leave.getId());
            ps.setInt(2, leave.getCompanyId());
            logger.info(SQLLogger.logSQL(sql, leave.getId(), leave.getCompanyId()));

            ps.executeUpdate();
        }
    }

    @Override
    public void deleteBatchLeave(List<Leave> leaves) throws SQLException {
        String sql = "update sys_approval_leave set state = 2, update_time = now() where id = ? and company_id = ? limit 1";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            for (Leave leave : leaves) {
                ps.setInt(1, leave.getId());
                ps.setInt(2, leave.getCompanyId());
                logger.info(SQLLogger.logSQL(sql, leave.getId(), leave.getCompanyId()));

                ps.addBatch();
            }
            ps.executeBatch();
        }
    }

    @Override
    public void deleteLeaveByRequestId(Integer requestId, Integer companyId) throws SQLException {
        String sql = "update sys_approval_leave set state = 2, update_time = now() where request_id = ? and company_id = ?";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, requestId);
            ps.setInt(2, companyId);
            logger.info(SQLLogger.logSQL(sql, requestId, companyId));

            ps.executeUpdate();
        }
    }

    @Override
    public Map<Integer, Integer> selectUserLeaveTimesMapByMonth(Integer companyId, Integer companyInfoId, String month) throws SQLException {
        Map<Integer, Integer> map = new HashMap<>();
        String sql = "select t.Fancestor_id, count(*) from request_flow f"
                + " left join sys_approval_leave l on f.Frequest_id = l.request_id"
                + " left join request_template t on t.Ftemplate_id = f.Frequest_templet"
                + " where l.company_id = ? and l.company_info_id = ? and date_format(f.Fcreate_time,'%Y-%m') = ? and l.state = 1"
                + " group by t.Fancestor_id";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setInt(2, companyInfoId);
            ps.setString(3, month);
            logger.info(SQLLogger.logSQL(sql, companyId, companyInfoId, month));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    map.put(rs.getInt(1), rs.getInt(2));
                }
            }
        }
        return map;
    }

    @Override
    public List<Leave> selectUserLeavesByLeaveType(Integer companyId, Integer companyInfoId, String leaveType) throws SQLException {
        List<Leave> list = new ArrayList<>();
        String sql = getQuerySql("where company_id = ? and company_info_id = ? and leave_type = ? and state = 1");
        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setInt(2, companyInfoId);
            ps.setString(3, leaveType);
            logger.info(SQLLogger.logSQL(sql, companyId, companyInfoId, leaveType));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(getObject(rs));
                }
            }
        }
        return list;
    }

    @Override
    public List<Leave> selectCompanyLeavesByDateScope(Integer companyId, String startTime, String endTime) throws SQLException {
        List<Leave> list = new ArrayList<>();
        String sql = getListQuerySql("where company_id = ? and not(real_end_time <= ? or real_start_time >= ?) and state = 1");

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setString(2, startTime);
            ps.setString(3, endTime);
            logger.info(SQLLogger.logSQL(sql, companyId, startTime, endTime));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(getListObject(rs));
                }
            }
        }
        return list;
    }

    @Override
    public List<Leave> selectCompanyLeaveDetailsByDateScope(Integer companyId, String startTime, String endTime) throws SQLException {
        List<Leave> list = new ArrayList<>();
        String sql = getQuerySql("where company_id = ? and not(real_end_time <= ? or real_start_time >= ?) and state = 1");

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setString(2, startTime);
            ps.setString(3, endTime);
            logger.info(SQLLogger.logSQL(sql, companyId, startTime, endTime));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(getObject(rs));
                }
            }
        }
        return list;
    }

    @Override
    public List<Leave> selectBatchUserLeavesByDateScope(Integer companyId, String companyInfoIds, String startTime, String endTime) throws SQLException {
        List<Leave> list = new ArrayList<>();
        String sql = getListQuerySql("where company_id = ? and company_info_id in(" + companyInfoIds + ")"
                + " and not(real_end_time <= ? or real_start_time >= ?) and state = 1");

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setString(2, startTime);
            ps.setString(3, endTime);
            logger.info(SQLLogger.logSQL(sql, companyId, startTime, endTime));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(getListObject(rs));
                }
            }
        }
        return list;
    }

    @Override
    public Map<Integer, Leave> selectLeavesByRequestIds(String requestIds) throws SQLException {
        Map<Integer, Leave> map = new HashMap<>();
        if (StringUtils.isEmpty(requestIds)) {
            return map;
        }
        String sql = getQuerySql("where request_id in(" + requestIds + ") order by id");

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            logger.info(sql);

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Leave leave = getObject(rs);
                    map.put(leave.getRequestId(), leave);
                }
            }
        }
        return map;
    }

}
